---------------------------------------------------------------
-- Session 1 (SQL/CLR)
---------------------------------------------------------------

---------------------------------------------------------------
-- UDF

using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class CoolFunctions
{
	[Microsoft.SqlServer.Server.SqlFunction]
	public static SqlString CoolEncrypt(SqlString Data)
	{
		char[] strArray = Data.ToString().ToCharArray();
		System.Array.Reverse(strArray);
		return new string(strArray);
	}

};

--

SELECT dbo.CoolEncrypt('SQL Server 2005 Rocks!')
SELECT dbo.CoolEncrypt('A man a plan a canal Panama')

---------------------------------------------------------------
-- Update the TEST.SQL and run with F5

---------------------------------------------------------------
-- Add a breakpoint and run with F5

---------------------------------------------------------------
-- Stored Procedure

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.IO;
using System.Xml;
using System.Xml.XPath;
using System.Xml.Xsl;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
	[Microsoft.SqlServer.Server.SqlProcedure]
	public static void spTransformXML(SqlXml rawXML, SqlXml rawXSLT)
	{
		SqlPipe pipe = SqlContext.Pipe;

		// Cast XML

		XmlDocument xmlDoc = new System.Xml.XmlDocument();
		xmlDoc.LoadXml(rawXML.Value);

		// Cast XSLT

		XmlDocument xslDoc = new System.Xml.XmlDocument();
		xslDoc.LoadXml(rawXSLT.Value);

		// Transform

		XslCompiledTransform xslt = new XslCompiledTransform();
		xslt.Load(xslDoc);
		Stream stream = new MemoryStream();
		XmlWriter wrt = new XmlTextWriter(stream,System.Text.Encoding.UTF8);
		xslt.Transform(xmlDoc, null, wrt);

		// Return HTML

		stream.Position = 0;
		StreamReader SR = new StreamReader(stream);
		pipe.Send(SR.ReadToEnd());
		SR.Close();
	}
};

---------------------------------------------------------------
-- Test

DECLARE @xml xml
SET @xml = '<?xml version="1.0" encoding="UTF-8"?>
<root>
     <person>
          <first>Richard</first>
          <last>Hundhausen</last>
          <title>Trainer</title>
     </person>
     <person>
          <first>Steven</first>
          <last>Borg</last>
          <title>Trainer</title>
     </person>
</root>'

DECLARE @xsl xml
SET @xsl = '<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format">
     <xsl:template match="root">
          <table border="1">
               <tr>
                    <td>First</td>
                    <td>Last</td>
                    <td>Title</td>
               </tr>
               <xsl:apply-templates/>
          </table>
     </xsl:template>
     <xsl:template match="person">
          <tr>
               <xsl:apply-templates/>
          </tr>
     </xsl:template>
     <xsl:template match="first">
          <td>
               <xsl:apply-templates/>
          </td>
     </xsl:template>
     <xsl:template match="last">
          <td>
               <xsl:apply-templates/>
          </td>
     </xsl:template>
     <xsl:template match="title">
          <td>
               <xsl:apply-templates/>
          </td>
     </xsl:template>
</xsl:stylesheet>'

exec spTransformXML @xml, @xsl

---------------------------------------------------------------
-- Debug this sproc using CLR Debugger (NO VS)

---------------------------------------------------------------
-- UDT

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
[System.Runtime.InteropServices.StructLayout(System.Runtime.InteropServices.LayoutKind.Sequential)]

public class DMS : INullable
{
	private Int16 dd, mm, ss;
	private bool bIsNull = true;

	public Int16 DD
	{
		get { return dd; }
		set { dd = value; }
	}

	public Int16 MM
	{
		get { return mm; }
		set { mm = value; }
	}

	public Int16 SS
	{
		get { return ss; }
		set { ss = value; }
	}

	public override string ToString()
	{
		if (bIsNull)
			return "null";
		else
			return String.Format("{0}{1}'{2}", dd, mm, ss);
	}

	public bool IsNull
	{
		get
		{
			return bIsNull;
		}
	}

	public static DMS Null
	{
		get
		{
			DMS dms = new DMS();
			return dms;
		}
	}

	public static DMS Parse(SqlString s)
	{
		if (s.IsNull || s.Value.ToLower().Equals("null"))
			return null;
		DMS dms = new DMS();
		string[] st = s.Value.Split('.');
		dms.DD = Int16.Parse(st[0]);
		dms.MM = Int16.Parse(st[1]);
		dms.SS = Int16.Parse(st[2]);
		dms.bIsNull = false;
		return dms;
	}
}

---------------------------------------------------------------
-- Test the UDT

CREATE TABLE EmployeeLocation (
  Employee int,
  Latitude DMS,
  Longitude DMS)
GO

INSERT EmployeeLocation VALUES (1,'15.55.00','37.15.12')
INSERT EmployeeLocation VALUES (2,'25.27.15','114.38.05')

SELECT Latitude.DD, Latitude.MM, Latitude.SS FROM EmployeeLocation

SELECT Latitude.ToString() FROM EmployeeLocation

-- Fails because the assembly isn't registered on the local machine

SELECT * FROM EmployeeLocation

INSERT EmployeeLocation VALUES (3,null,null)

SELECT Latitude.ToString() FROM EmployeeLocation
